﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using Maticsoft.DBUtility;
namespace BLL
{
    public class UserPersonBLL
    {
        public static DataSet FindPersonDetailByID(string id)
        {
            string sqlstr = "select * from user_person where usermemberid = " + id;
            return DbHelperOra.Query(sqlstr);
        }

        public static int AddUpdateUserPerson(int id, string dblNo, string nickName,
          string fullName, string keyWord1, string keyWord2, string keyWord3,string shenfenzheng,
              string address, string description, string phone, string fax,
                  string email, string mobilePhone,  string qq, string msn,
                      string blog, string weibo, string website)//infostate默认为0,infotime默认为当前时间
        {
            string sqlselect = "select * from user_person where usermemberid = " + id;
            DataSet dsSelect = DbHelperOra.Query(sqlselect);
            if (dsSelect.Tables[0].Rows.Count == 0)
            {
                //add
                return AddUserPerson(id,dblNo, nickName, fullName, keyWord1, keyWord2, keyWord3,shenfenzheng,
                    address, description, phone, fax, email, mobilePhone, 
                    qq, msn, blog, weibo, website);
            }
            else
            {
                //update

                return UpdateUserPerson(id,  nickName, fullName, keyWord1, keyWord2, keyWord3,shenfenzheng,
                    address, description, phone, fax, email, mobilePhone, 
                    qq, msn, blog, weibo, website);
            }
        }

        private static int AddUserPerson(int id,string dblNo, string nickName,
           string fullName, string keyWord1, string keyWord2, string keyWord3,string shenfenzheng,
               string address, string description, string phone, string fax,
                   string email, string mobilePhone, string qq, string msn,
                       string blog, string weibo, string website)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"insert into user_person (usermemberid,dblno,nickname,fullname,keyword1,
                        keyword2,keyword3,shenfenzheng,address,description,phone,fax,email,mobilephone,qq,
                        msn,blog,weibo,website,infostate,infotime) ");/*比company多一个shenfenzheng*/
            sb.Append(@"values(:v_usermemberid,:v_dblno,:v_nickname,:v_fullname,:v_keyword1,
                        :v_keyword2,:v_keyword3,:v_shenfenzheng,:v_address,:v_description,:v_phone,:v_fax,
                        :v_email,:v_mobilephone,:v_qq,:v_msn,
                        :v_blog,:v_weibo,:v_website,:v_infostate,:v_infotime) ");

            OracleParameter[] para = {
                new OracleParameter(":v_dblno",OracleType.VarChar),
                new OracleParameter(":v_nickname",OracleType.VarChar),
                new OracleParameter(":v_fullname",OracleType.VarChar),
                new OracleParameter(":v_keyword1",OracleType.VarChar),
                new OracleParameter(":v_keyword2",OracleType.VarChar),
                new OracleParameter(":v_keyword3",OracleType.VarChar),
                new OracleParameter(":v_address",OracleType.VarChar),
                new OracleParameter(":v_description", OracleType.VarChar),
                new OracleParameter(":v_phone",OracleType.VarChar),
                new OracleParameter(":v_fax",OracleType.VarChar),
                new OracleParameter(":v_email",OracleType.VarChar),
                new OracleParameter(":v_mobilephone",OracleType.VarChar),
                new OracleParameter(":v_qq",OracleType.VarChar),
                new OracleParameter(":v_msn",OracleType.VarChar),
                new OracleParameter(":v_blog",OracleType.VarChar),
                new OracleParameter(":v_weibo",OracleType.VarChar),
                new OracleParameter(":v_website",OracleType.VarChar),
                new OracleParameter(":v_infostate",OracleType.VarChar),
                new OracleParameter(":v_infotime",OracleType.DateTime),
                new OracleParameter(":v_shenfenzheng",OracleType.VarChar),
                new OracleParameter(":v_usermemberid",OracleType.Number)
            };
            para[0].Value = dblNo;
            para[1].Value = nickName;
            para[2].Value = fullName;
            para[3].Value = keyWord1;
            para[4].Value = keyWord2;
            para[5].Value = keyWord3;
            para[6].Value = address;
            para[7].Value = description;
            para[8].Value = phone;
            para[9].Value = fax;
            para[10].Value = email;
            para[11].Value = mobilePhone;
            para[12].Value = qq;
            para[13].Value = msn;
            para[14].Value = blog;
            para[15].Value = weibo;
            para[16].Value = website;
            para[17].Value = "0";
            para[18].Value = System.DateTime.Now;
            para[19].Value = shenfenzheng;
            para[20].Value = id;
            return DbHelperOra.ExecuteSql(sb.ToString(), para);
        }

        private static int UpdateUserPerson(int usermemberid, string nickName,
           string fullName, string keyWord1, string keyWord2, string keyWord3,string shenfenzheng,
               string address, string description, string phone, string fax,
                   string email, string mobilePhone,  string qq, string msn,
                       string blog, string weibo, string website)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"update user_Person
                           set 
                               
                               nickname = :v_nickname,
                               fullname = :v_fullname,
                               keyword1 = :v_keyword1,
                               keyword2 = :v_keyword2,
                               keyword3 = :v_keyword3,
                               shenfenzheng = :v_shenfenzheng,
                               address = :v_address,
                               description = :v_description,
                               phone = :v_phone,
                               fax = :v_fax,
                               email = :v_email,
                               mobilephone = :v_mobilephone,
                               infostate = :v_infostate,
                               infotime = :v_infotime,
                               qq = :v_qq,
                               msn = :v_msn,
                               blog = :v_blog,
                               weibo = :v_weibo,
                               website = :v_website
                         where usermemberid = :v_usermemberid");
            //不能修改dblno
            OracleParameter[] para = {
               
                new OracleParameter(":v_nickname",OracleType.VarChar),
                new OracleParameter(":v_fullname",OracleType.VarChar),
                new OracleParameter(":v_keyword1",OracleType.VarChar),
                new OracleParameter(":v_keyword2",OracleType.VarChar),
                new OracleParameter(":v_keyword3",OracleType.VarChar),
                new OracleParameter(":v_address",OracleType.VarChar),
                new OracleParameter(":v_description", OracleType.VarChar),
                new OracleParameter(":v_phone",OracleType.VarChar),
                new OracleParameter(":v_fax",OracleType.VarChar),
                new OracleParameter(":v_email",OracleType.VarChar),
                new OracleParameter(":v_mobilephone",OracleType.VarChar),
                new OracleParameter(":v_qq",OracleType.VarChar),
                new OracleParameter(":v_msn",OracleType.VarChar),
                new OracleParameter(":v_blog",OracleType.VarChar),
                new OracleParameter(":v_weibo",OracleType.VarChar),
                new OracleParameter(":v_website",OracleType.VarChar),
                new OracleParameter(":v_infostate",OracleType.VarChar),
                new OracleParameter(":v_infotime",OracleType.DateTime),
                new OracleParameter(":v_usermemberid",OracleType.Number),
                new OracleParameter(":v_shenfenzheng",OracleType.VarChar)
            };
            
            para[0].Value = nickName;
            para[1].Value = fullName;
            para[2].Value = keyWord1;
            para[3].Value = keyWord2;
            para[4].Value = keyWord3;
            para[5].Value = address;
            para[6].Value = description;
            para[7].Value = phone;
            para[8].Value = fax;
            para[9].Value = email;
            para[10].Value = mobilePhone;
            para[11].Value = qq;
            para[12].Value = msn;
            para[13].Value = blog;
            para[14].Value = weibo;
            para[15].Value = website;
            para[16].Value = "0";
            para[17].Value = System.DateTime.Now;
            para[18].Value = usermemberid;
            para[19].Value = shenfenzheng;
            return DbHelperOra.ExecuteSql(sb.ToString(), para);
        }

        public static int ApproveUserPerson(int usermemberid)
        {
            string sqlstr = @"update user_person 
                                set infostate = :v_infostate,
                               infotime = :v_infotime where usermemberid = :v_usermemberid";
            OracleParameter[] para = {new OracleParameter(":v_infostate",OracleType.VarChar),
                                      new OracleParameter(":v_infotime",OracleType.DateTime),
                                     new OracleParameter(":v_usermemberid",OracleType.Number)};
            para[0].Value = "1";//已审核通过
            para[1].Value = System.DateTime.Now;
            para[2].Value = usermemberid;
            return DbHelperOra.ExecuteSql(sqlstr, para);
        }

        public static int NotApproveUserPerson(int usermemberid)
        {
            string sqlstr = @"update user_Person 
                                set infostate = :v_infostate,
                               infotime = :v_infotime where usermemberid = :v_usermemberid";
            OracleParameter[] para = {new OracleParameter(":v_infostate",OracleType.VarChar),
                                      new OracleParameter(":v_infotime",OracleType.DateTime),
                                     new OracleParameter(":v_usermemberid",OracleType.Number)};
            para[0].Value = "0";//已审核不通过
            para[1].Value = System.DateTime.Now;
            para[2].Value = usermemberid;
            return DbHelperOra.ExecuteSql(sqlstr, para);
        }
    }
}
